Excel BI - Excel Challenge 660

excel-challenges
excel-formulas
🔰 Insert the sum for the year after the year ends.
Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 660

Challenge Description

🔰 Insert the sum for the year after the year ends.

Solutions

library(tidyverse)
library(readxl)

path = "Excel/660 Insert Sum After Year Ends.xlsx"
input = read_excel(path, range = "A2:B13")
test  = read_excel(path, range = "D2:E16")

result = input %>%
  separate(`Year-Quarter`, into = c("Year-Quarter", "Quarter"), sep = "-") %>%
  summarise(Amount = sum(Amount), .by = `Year-Quarter`) %>%
  mutate(`Year-Quarter` = paste0(`Year-Quarter`, "-Q5")) %>%
  bind_rows(input) %>%
  arrange(`Year-Quarter`) %>%
  mutate(`Year-Quarter` = ifelse(str_detect(`Year-Quarter`, "Q5"), NA, `Year-Quarter`))

all.equal(result, test, check.attributes = FALSE)
# [1] TRUE
  • Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns; Parse the packed text or string structure; Aggregate or rank the data at the required grouping level.
  • Strengths: The code maps the workbook rule into a compact, reproducible pipeline.
  • Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
  • Gem: The elegant part is how little code is needed once the correct intermediate representation is chosen.
import pandas as pd
import numpy as np

path = "660 Insert Sum After Year Ends.xlsx"
input = pd.read_excel(path, usecols="A:B", skiprows=1, nrows=11)
test = pd.read_excel(path, usecols="D:E", skiprows=1, nrows=14).rename(columns=lambda x: x.replace('.1', ''))

input[['Year-Quarter', 'Quarter']] = input['Year-Quarter'].str.split('-', expand=True)
result = input.groupby('Year-Quarter', as_index=False)['Amount'].sum()
result['Quarter'] = 'Q5'
result = pd.concat([input, result], ignore_index=True)
result['Year-Quarter'] = result['Year-Quarter'] + '-' + result['Quarter']
result = result.sort_values(by='Year-Quarter') 
result.loc[result['Quarter'] == 'Q5', 'Year-Quarter'] = np.NaN
result = result.drop(columns=['Quarter']).reset_index(drop=True)

print(result.equals(test)) # True

The Python version follows the same grouped logic and keeps the transformation explicit in a dataframe pipeline.

Difficulty Level

Easy / Medium

The business rule is clear, though the workbook still needs a few transformation steps to reach the expected output.